package Drivers;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URI;
import java.net.URL;
import java.nio.charset.Charset;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.LinkedList;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONTokener;

import DBPack.BusStation;
import DBPack.BusStationDetail;
import DBPack.BusStationTime;
import DBPack.DBStatement;
import DBPack.Bus;
import DBPack.Line;

public class BusDriver {

	public static Bus getBusById(int routeId) {
		try{
			String query="SELECT * FROM routes where `route_id`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setInt(1, routeId);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return new Bus(result.getInt("route_id"), result.getString("route_code"),result.getString("route_name"),result.getString("start_point"),result.getString("end_point"),result.getString("type"),result.getString("duration"));
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Bus getBusByRouteCode(String routeCode) {
		try{
			String query="SELECT * FROM routes where `route_code`= ?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, routeCode);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return new Bus(result.getInt("route_id"), result.getString("route_code"),result.getString("route_name"),result.getString("start_point"),result.getString("end_point"),result.getString("type"),result.getString("duration"));
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Bus[] getAllBusList() {
		try{
			String query="SELECT * FROM routes " ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertBusToArray(result);
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	public static Bus[] getBusList(String input) {
		try{
			String query="SELECT * FROM routes where route_code like ? or route_name like ? order by route_code asc,route_name asc";
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, input+"%");
			ps.setString(2, input+"%");
			ResultSet result = ps.executeQuery();
			if(result.next())
				return convertBusToArray(result);
		}  catch(SQLException e) {
		} catch (Exception e) {
		}
		return null;
	}
	
	public static Bus[] getBusesByStationCode(String stationCode) {
		try{
			System.out.println("deniyorum");
			String query="SELECT DISTINCT r.* FROM routes r INNER JOIN station_details s " +
					"On r.`route_code`=s.`route_code` WHERE s.`station_code`= ?";
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, stationCode);
			ResultSet set = ps.executeQuery();
			if(set.next())
				return convertBusToArray(set);
		}  catch(SQLException e) {
				e.printStackTrace();
				return null;
		} catch (Exception e) {
		}
		return new Bus[0];
	}
	public static BusStationDetail[] getBusStationList(String routeCode) {
		try{
			String query="SELECT * FROM station_details where route_code=?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, routeCode);
			ResultSet set = ps.executeQuery();
			if(set.next())
				return convertBusStationDetailToArray(set);
		}  catch(SQLException e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		} catch (Exception e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		}
		return new BusStationDetail[0];
		
	}
	public static BusStationDetail[] getStationsByBusCode(String routeCode) {
		try{
			String query="SELECT * FROM station_details where route_code=?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, routeCode);
			ResultSet set = ps.executeQuery();
			if(set.next())
				return convertBusStationDetailToArray(set);
		}  catch(SQLException e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		} catch (Exception e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		}
		return new BusStationDetail[0];
		
	}
	public static BusStationDetail[] getStationsByBusCode(String routeCode,String routeDirection) {
		try{
			String query="SELECT * FROM station_details where route_code=? and route_direction=?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, routeCode);
			ps.setString(2, routeDirection);
			ResultSet set = ps.executeQuery();
			if(set.next())
				return convertBusStationDetailToArray(set);
		}  catch(SQLException e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		} catch (Exception e) {
			e.printStackTrace();
			return new BusStationDetail[0];
		}
		return new BusStationDetail[0];
		
	}
	public static BusStationTime[] getBusStationTimeList(String routeCode,String stationCode,String direction) throws Exception {
		  LinkedList<BusStationTime> resultList=new LinkedList<BusStationTime>();
		  String json = readUrl("http://nbo.develioglu.com/getLineStopSchedules.aspx?line="+routeCode+"&stop="+stationCode+"&direction"+direction+"&output=json");
		  String[] hourTypes={"SaturdayHours","SundayHours","WorkHours"};
		  String[] hourCode={"C","P","H"};
		  System.out.println(json);
		  String hour,jsonDirection;
		  JSONArray list;
		  JSONObject scheduleList;
		  JSONObject line=new JSONObject(new JSONTokener(json));
		  if (json.contains("Hour")) {
			  scheduleList=((JSONArray)line.getJSONObject("Line").getJSONObject("StopContainers").getJSONObject("StopContainer").getJSONObject("Stop").getJSONObject("Schedules").get("Schedule")).getJSONObject(0);
			  if (scheduleList != null) {
					  jsonDirection=scheduleList.getString("Departure");
					  for (int j=0;j<hourTypes.length;j++) {
						    list=(JSONArray)scheduleList.get(hourTypes[j]);
						    for (int i=0;i<list.length();i++) {
						    	hour=list.getJSONObject(i).getString("Hour");
						    	resultList.add(new BusStationTime(routeCode, stationCode, jsonDirection, convertStringToTime(hour), hourTypes[j]));
							    System.out.println(routeCode+" "+stationCode+" "+jsonDirection + " "+ hour + " "+hourCode[j]);
						    }
					  }
			  }
		  }
		  return resultList.toArray(new BusStationTime[resultList.size()]);

		  
	}
	public static BusStationTime[] getBusStationTimeList(String routeCode,String stationCode) throws Exception {
		  LinkedList<BusStationTime> resultList=new LinkedList<BusStationTime>();
		  String json = readUrl("http://nbo.develioglu.com/getLineStopSchedules.aspx?line="+routeCode+"&stop="+stationCode+"&output=json");
		  String[] hourTypes={"SaturdayHours","SundayHours","WorkHours"};
		
		  String[] hourCode={"C","P","H"};
		  String hour,jsonDirection;
		  JSONArray list,scheduleList;
		  JSONObject line=new JSONObject(new JSONTokener(json));
		  if (json.contains("Hour")) {
			  scheduleList=(JSONArray)line.getJSONObject("Line").getJSONObject("StopContainers").getJSONObject("StopContainer").getJSONObject("Stop").getJSONObject("Schedules").get("Schedule");
			  if (scheduleList != null) {
				  for (int z=0;z<scheduleList.length();z++) {
					  jsonDirection=scheduleList.getJSONObject(z).getString("Departure");
					  for (int j=0;j<hourTypes.length;j++) {
						    list=(JSONArray)scheduleList.getJSONObject(z).get(hourTypes[j]);
						    for (int i=0;i<list.length();i++) {
						    	hour=list.getJSONObject(i).getString("Hour");
						    	resultList.add(new BusStationTime(routeCode, stationCode, jsonDirection, convertStringToTime(hour), hourTypes[j]));
							    System.out.println(routeCode+" "+stationCode+" "+jsonDirection + " "+ hour + " "+hourCode[j]);
						    }
					  }
				  }
			  }
		  }
		  return resultList.toArray(new BusStationTime[resultList.size()]);
	}
	public static BusStationTime[] getBusStationTimeListByOrder(String routeCode,String direction,int stationOrder) throws Exception {
		
		  String stationCode=getStationCode(routeCode,direction,stationOrder);
		  LinkedList<BusStationTime> resultList=new LinkedList<BusStationTime>();
		  String json = readUrl("http://nbo.develioglu.com/getLineStopSchedules.aspx?line="+routeCode+"&stop="+stationCode+"&direction"+direction+"&output=json");
		  String[] hourTypes={"SaturdayHours","SundayHours","WorkHours"};
		
		  String[] hourCode={"C","P","H"};
		  String hour,jsonDirection;
		  JSONArray list,scheduleList;
		  JSONObject line=new JSONObject(new JSONTokener(json));
		  if (json.contains("Hour")) {
			  scheduleList=(JSONArray)line.getJSONObject("Line").getJSONObject("StopContainers").getJSONObject("StopContainer").getJSONObject("Stop").getJSONObject("Schedules").get("Schedule");
			  if (scheduleList != null) {
				  for (int z=0;z<scheduleList.length();z++) {
					  jsonDirection=scheduleList.getJSONObject(z).getString("Departure");
					  for (int j=0;j<hourTypes.length;j++) {
						    list=(JSONArray)scheduleList.getJSONObject(z).get(hourTypes[j]);
						    for (int i=0;i<list.length();i++) {
						    	hour=list.getJSONObject(i).getString("Hour");
						    	resultList.add(new BusStationTime(routeCode, stationCode, jsonDirection, convertStringToTime(hour), hourTypes[j]));
							    System.out.println(routeCode+" "+stationCode+" "+jsonDirection + " "+ hour + " "+hourCode[j]);
						    }
					  }
				  }
			  }
		  }
		  return resultList.toArray(new BusStationTime[resultList.size()]);
	}
	private static String getStationCode(String routeCode,String direction,int stationOrder) {
		try{
			String query="SELECT * FROM station_details where route_code=? and route_direction=? and station_order=?" ;
			PreparedStatement ps=(PreparedStatement) DBStatement.getMainConnection().prepareStatement(query);
			ps.setString(1, routeCode);
			ps.setString(2, direction);
			ps.setInt(3, stationOrder);
			ResultSet set = ps.executeQuery();
			if(set.next())
				return set.getString("station_code");
		}  catch(SQLException e) {
			e.printStackTrace();
			return "";
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
		return "";
		
		
		
		
	}
	private static Bus[] convertBusToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		Bus[] tags = new Bus[N];
		int i=0;
		while(result.next())
			tags[i++] = new Bus(result.getInt("route_id"), result.getString("route_code"),result.getString("route_name"),result.getString("start_point"),result.getString("end_point"),result.getString("type"),result.getString("duration"));
		return tags;

	}
	private static BusStation[] convertBusStationToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		BusStation[] list = new BusStation[N];
		int i=0;
		while(result.next())
			list[i++] = new BusStation(result.getInt("station_id"), result.getString("route_code"),result.getString("route_name"));
		return list;

	}
	private static BusStationDetail[] convertBusStationDetailToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		BusStationDetail[] list = new BusStationDetail[N];
		int i=0;
		while(result.next())
			list[i++] = new BusStationDetail(result.getInt("station_details_id"), result.getString("station_code"),result.getString("station_name"),result.getString("route_code"),result.getString("route_direction"),result.getInt("station_order"),result.getString("latitude"),result.getString("longtitude"),result.getString("location"));
		return list;

	}
	private static BusStationTime[] convertBusStationTimeToArray(ResultSet result) throws SQLException{
		int N = 0;
		result.beforeFirst();
		while(result.next()) N++;
		result.beforeFirst();
		BusStationTime[] list = new BusStationTime[N];
		int i=0;
		while(result.next())
			list[i++] = new BusStationTime(result.getInt("station_time_id"), result.getString("route_code"),result.getString("station_code"),result.getString("route_direction"),result.getTime("time"),result.getString("day_code"));
		return list;

	}
	private static String readUrl(String urlString) throws Exception {
        BufferedReader reader = null;
        try {
        	
        	URI uri=new URI(urlString);
            URL url = new URL(uri.toASCIIString());
            System.out.println(url.getQuery());
            HttpURLConnection connection = (HttpURLConnection)url.openConnection();
            connection.setRequestMethod("GET");
            connection.setDoOutput(true);
            connection.setReadTimeout(2000);
            connection.setChunkedStreamingMode(0);
            connection.setRequestProperty("Content-Language", "en-US");
            reader = new BufferedReader(new InputStreamReader(connection.getInputStream(),Charset.forName("UTF-8")));
           
            StringBuffer buffer = new StringBuffer();
            String read;
            while ((read = reader.readLine()) !=null)
                buffer.append(read); 

            return buffer.toString();
        } catch(Exception e) {
        	e.printStackTrace();
        	return "";
        } finally {
            if (reader != null)
                reader.close();
        }

    }
	public static Time convertStringToTime(String hourAndMinute) {
		   int hour=Integer.parseInt(hourAndMinute.split(":")[0]);
		   int minute=Integer.parseInt(hourAndMinute.split(":")[1]);
		   return new Time(hour,minute,0);
	}
	public static void printBusList(Bus[] list) {
		Bus current;
		for (int i=0;i<list.length;i++) {
			current=list[i];
			System.out.println(current.getRouteCode()+" - "+current.getRouteName()+" - "+current.getType());
		}
	}
	public static void printBusStationDetailList(BusStationDetail[] list) {
		BusStationDetail current;
		for (int i=0;i<list.length;i++) {
			current=list[i];
			System.out.println(current.getRouteCode()+" - "+current.getRouteDirection()+ "- "+current.getStationName()+" - "+current.getStationOrder());
		}
	}

}
